<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2007-2008 Starbugs (univis2typo3@googlegroups.com)
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *  A copy is found in the textfile GPL.txt and important notices to the license
 *  from the author is found in LICENSE.txt distributed with these scripts.
 *
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/
/**
 * Module: Web>UnivIS2Typo3
 *
 * Description:
 * 	Creation of "tx_u2t3_"-tables with attached uid.
 * 	The table "tx_u2t3_uid_key" mananges the relations between them.
 *	pid == department_id, so it's not needed. But it would be _no_ problem
 * 	to rename it.. :%s/department_id/pid/g
 *
 * Notice: 
 *
 *  
 * @author	Starbugs <univis2typo3@googlegroups.com>
 * @author	Johannes Held <univis2typo3@googlegroups.com>
 */

class UnivisTableManager_createtx
{ 
    function create_db_schema()
    {
		$sql_query = array();
		$sql_query[]="SET GLOBAL group_concat_max_len = 16384";
	
		$sql_query[] ="CREATE TABLE IF NOT EXISTS tx_u2t3_uid_key (
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER NOT NULL auto_increment,
			`key` VARCHAR(255) NOT NULL,
	
			PRIMARY KEY (uid,`key`)
			)";
	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_person (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255),
			type VARCHAR(50),
		    `group` VARCHAR(50),
			atitle VARCHAR(50),
			firstname VARCHAR(100),
			`from` VARCHAR(255),
			id VARCHAR(255),
			lastname VARCHAR(100),
			lehr VARCHAR(255),
			lehraufg VARCHAR(255),
			lehrtyp VARCHAR(255),
			pgroup VARCHAR(255),
			shortname VARCHAR(255),
			title VARCHAR(255),
			univis_key VARCHAR(255),
			until VARCHAR(255),
			visible VARCHAR(255),
			work VARCHAR(255),
			zweitmgl VARCHAR(255),
			alumni VARCHAR(255),
			chef VARCHAR(255),
			founder VARCHAR(255),
			name VARCHAR(255),
			current ENUM ( '0', '1' ) DEFAULT '0',
			gender SET ( 'female', 'male' ),
			PRIMARY KEY(`key`)
			    )";
		//    projects := ( HUMAINE, IMPRS, PF-STAR, SFB-539, SFB-603, SVcheck, SmartWeb )* -> tx_u2t3_person_research,
		// type := acad, admin, azubi, externalPhD, guest, info, secretary, trainee, webmaster

		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_research (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			contact VARCHAR(255) references tx_u2t3_person, 
			description TEXT,
			description_en TEXT,
			enddate VARCHAR(255),
			orgname VARCHAR(255),
			keywords VARCHAR(255),
			keywords_en VARCHAR(255),
			startdate VARCHAR(255),
			title VARCHAR(255),
			title_en VARCHAR(255),
			url VARCHAR(255),
			url_en VARCHAR(255)
	
			)";

		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_title (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			parent_title_key VARCHAR(255) references tx_u2t3_title,
			title VARCHAR(255),
			title_en VARCHAR(255),
			text TEXT,
			text_en TEXT,
			ordernr VARCHAR(255),
			parent_title VARCHAR(255)
			)";
	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_room (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			address VARCHAR(255),
			description VARCHAR(255),
			anst VARCHAR(255),
			audio VARCHAR(255),
			beam VARCHAR(255),
			buildno VARCHAR(255),
			dark VARCHAR(255),
			dia VARCHAR(255),
			fest VARCHAR(255),
			`id` VARCHAR(255),
			inet VARCHAR(255),
			lose VARCHAR(255),
			name VARCHAR(255),
			ohead VARCHAR(255),
			roomno VARCHAR(255),
			short VARCHAR(255),
			size VARCHAR(255),
			tafel VARCHAR(255),
			tel VARCHAR(255),
			url VARCHAR(255),
			vcr VARCHAR(255)
			    )";

	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_lecture (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255),
			semester VARCHAR(6),
			classification VARCHAR(255) references tx_u2t3_title,
			parent_lv VARCHAR(255) references tx_u2t3_lecture,
			room VARCHAR(255) references tx_u2t3_room,
			organizational VARCHAR(255),
			summary TEXT,
			ects_summary TEXT,
			name VARCHAR(255),
			ects_name VARCHAR(255),
	
			beginners VARCHAR(255),
			benschein VARCHAR(255),
			comment VARCHAR(255),
			ects VARCHAR(255),
			ects_cred VARCHAR(255),
			ects_literature VARCHAR(255),
			ects_organizational VARCHAR(255),
			evaluation VARCHAR(255),
			fachstud VARCHAR(255),
			fruehstud VARCHAR(255),
			gast VARCHAR(255),
			id VARCHAR(255),
			internat VARCHAR(255),
			keywords VARCHAR(255),
			literature VARCHAR(255),
			ordernr VARCHAR(255),
			regsystem VARCHAR(255),
			schein VARCHAR(255),
			short VARCHAR(255),
			startdate VARCHAR(255),
			sws VARCHAR(255),
			time_description VARCHAR(255),
			turnout VARCHAR(255),
			type VARCHAR(255),
			url_description VARCHAR(255),
			PRIMARY KEY (`key`, `semester`)
			    )";
		// TODO
		//    courses , -> tx_u2t3_lecture_courses
		//    dozs , -> tx_u2t3_lecture_dozs
		//    literature , -> tx_u2t3_lecture_literature
		//    studs , -> tx_u2t3_lecture_stud
		//    terms , -> tx_u2t3_lecture_term
		//    parent-lv , -> parent_lecture ?
	

	

		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_thesis ( 
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			title VARCHAR(255),
			notice VARCHAR(255),
			topic VARCHAR(255),
			finishdate VARCHAR(255),
			finishyear VARCHAR(255),
			firstname VARCHAR(255),
			keywords VARCHAR(255),
			lastname VARCHAR(255),
			prerequisit VARCHAR(255),
			public VARCHAR(255),
			registerdate VARCHAR(255),
			reservedate VARCHAR(255),
			short VARCHAR(255),
			status VARCHAR(255),
			type VARCHAR(255),
			url VARCHAR(255),
			visible VARCHAR(255)
			)";

	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_pub ( 
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			pubtitle VARCHAR(255),
			address VARCHAR(255),
			booktitle VARCHAR(255),
			conf_url VARCHAR(255),
			conference VARCHAR(255),
			edition VARCHAR(255),
			hstype VARCHAR(255),
			hsyear VARCHAR(255),
			id VARCHAR(255),
			isbn VARCHAR(255),
			issn VARCHAR(255),
			journal VARCHAR(255),
			keywords VARCHAR(255),
			number VARCHAR(255),
			pages VARCHAR(255),
			plocation VARCHAR(255),
			publisher VARCHAR(255),
			puburl VARCHAR(255),
			school VARCHAR(255),
			series VARCHAR(255),
			servolume VARCHAR(255),
			type VARCHAR(255),
			volume VARCHAR(255),
			year VARCHAR(255)
			    )";

	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_job ( 
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			parent_key VARCHAR(255),
			description VARCHAR(255) PRIMARY KEY,
			description_en VARCHAR(255),
			flags VARCHAR(255)
			)";
	
		$sql_query[]="CREATE TABLE IF NOT EXISTS tx_u2t3_org (
			department_id INTEGER,	
			pid INTEGER NOT NULL DEFAULT 1,
			uid INTEGER references tx_u2t3_uid_key,
			`key` VARCHAR(255) PRIMARY KEY,
			b_lehre VARCHAR(255),
			b_orgdesc VARCHAR(255),
			b_rescoop VARCHAR(255),
			b_resequip VARCHAR(255),
			b_resfoc VARCHAR(255),
			email VARCHAR(255),
			fax VARCHAR(255),
			id VARCHAR(255),
			name VARCHAR(255),
			name_en VARCHAR(255),
			ordernr VARCHAR(255),
			orgdesc VARCHAR(255),
			orgdesc_en VARCHAR(255),
			orgnr VARCHAR(255),
			ort VARCHAR(255),
			pubser VARCHAR(255),
			pubser_en VARCHAR(255),
			resconf VARCHAR(255),
			resconf_en VARCHAR(255),
			rescoop VARCHAR(255),
			rescoop_en VARCHAR(255),
			resequip VARCHAR(255),
			resequip_en VARCHAR(255),
			resfoc VARCHAR(255),
			resfoc_en VARCHAR(255),
			street VARCHAR(255),
			tel VARCHAR(255),
			url VARCHAR(255)
			    )";

		foreach ($sql_query as $q)
		{
		    $result = $GLOBALS['TYPO3_DB']->sql_query($q);
		    if(!$result) {
		        echo(mysql_error() . "<br />" . $q . "<br />");
		    }
		}
    } //create_db_schema

    function create_db_static_tables()
    {
		$sql_query = array();
		// Publication order
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_pub_type_order";
	
		$sql_query[]="CREATE TABLE tx_u2t3_pub_type_order (
			`key` int NOT NULL auto_increment,
			sorting int NOT NULL,
			type varchar(255) NOT NULL,
			description varchar(255) NOT NULL,
			description_en varchar(255) NOT NULL,
			PRIMARY KEY  (`key`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin1";
	
	
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (1, 1, 'monogr', 'Monographien', 'Monographs')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (2, 2, 'artmono', 'Monographien (Artikel)', 'Monographs (article)')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (3, 3, 'tagband', 'Tagungsband', 'Conference Proceedings')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (4, 4, 'artzeit', 'Zeitschrift', 'Journals')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (5, 5, 'arttagu', 'Tagung', 'Conferences')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (6, 6, 'hschri', 'Hochschulschriften', 'Theses')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (7, 7, 'talk', 'Vortrag', 'Talks')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (8, 8, 'techrep', 'Technischer Report', 'Technical Reports')";
		$sql_query[]="INSERT INTO tx_u2t3_pub_type_order VALUES (9, 9, 'schutzr', 'Schutzrechte', 'Patents')";
			
		// Our Team order
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_research_group";
	
		$sql_query[]="CREATE TABLE tx_u2t3_research_group (
			`uid` int NOT NULL auto_increment,
			`sorting` int NOT NULL,
			`job` varchar(255) NOT NULL,
			`description` varchar(255) NOT NULL,
			`description_en` varchar(255) NOT NULL,
			`job_url` varchar(255) NOT NULL,
			`job_url_en` varchar(255) NOT NULL,
			`display` BOOL NOT NULL, 
			`is_group` BOOL NOT NULL, 
			`media` varchar(255) NOT NULL,
			`introtext` text NOT NULL,
			PRIMARY KEY  (`uid`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin1";

		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 1,  1, 'Leitung', 'Leitung des Lehrstuhls', 'Head of the Pattern Recognition Lab', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 2,  2, 'Emeritus', 'Emeritus', 'Emeritus', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 3,  3, 'Sekretariat', 'Sekretariat', 'Secretaries', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 4,  4, 'Vorzimmer', 'Sekretariat', 'Secretaries', '', '', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 5,  5, 'Leitung Medizinische Bildregistrierung', 'Leitung Medizinische Bildregistrierung', 'Head of Medical Image Registration', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 6,  6, 'Medizinische Bildregistrierung', 'Medizinische Bildregistrierung', 'Medical Image Registration', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 7,  7, 'Leitung Medizinische Bildrekonstruktion', 'Leitung Medizinische Bildrekonstruktion', 'Head of Medical Image Reconstruction', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 8,  8, 'Medizinische Bildrekonstruktion', 'Medizinische Bildrekonstruktion', 'Medical Image Reconstruction', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES( 9,  9, 'Leitung Medizinische Bildsegmentierung', 'Leitung Medizinische Bildsegmentierung', 'Head of Medical Image Segmentation', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(10, 10, 'Medizinische Bildsegmentierung', 'Medizinische Bildsegmentierung', 'Medical Image Segmentation', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(11, 11, 'Leitung Ophthalmologische Bildgebung', 'Leitung Ophthalmologische Bildgebung', 'Head of Ophthalmic Imaging', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(12, 12, 'Ophthalmologische Bildgebung', 'Ophthalmologische Bildgebung', 'Ophthalmic Imaging', '/de/research/medical-image-processing/', '/research/medical-image-processing/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(13, 13, 'Leitung Rechnersehen', 'Leitung Rechnersehen', 'Head of Computer Vision', '/de/research/computer-vision/', '/research/computer-vision/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(14, 14, 'Rechnersehen', 'Rechnersehen', 'Computer Vision', '/de/research/computer-vision/', '/research/computer-vision/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(15, 15, 'Leitung Sprachverarbeitung', 'Leitung Sprachverarbeitung', 'Head of Speech Processing and Understanding', '/de/research/speech-processing-and-understanding/', '/research/speech-processing-and-understanding/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(16, 16, 'Sprachverarbeitung', 'Sprachverarbeitung', 'Speech Processing and Understanding', '/de/research/speech-processing-and-understanding/', '/research/speech-processing-and-understanding/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(17, 17, 'Leitung Digitaler Sport', 'Leitung Digitaler Sport und Sportronics', 'Head of Digital Sports and Sportronics', '/de/research/digital-sports-and-sportronics/', '/research/digital-sports-and-sportronics/', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(18, 18, 'Digitaler Sport', 'Digitaler Sport und Sportronics', 'Digital Sports and Sportronics', '/de/research/digital-sports-and-sportronics/', '/research/digital-sports-and-sportronics/', 1, 1, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(19, 19, 'Wiss. Mitarbeiter', 'Wissenschaftlicher Mitarbeiter', 'Researcher', '', '', 0, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(20, 20, 'externe Doktoranden', 'Externe Doktoranden', 'External PhD Students', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(21, 21, 'Praktikanten', 'Praktikanten', 'Interns', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(22, 22, 'Nichtwiss. Personal', 'Nichtwissenschaftliches Personal', 'Technical/Administrative Staff', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(23, 23, 'Gast', 'G&auml;ste', 'Guests', '', '', 1, 0, '', '');";
		$sql_query[]="INSERT INTO `tx_u2t3_research_group` VALUES(24, 24, 'ehemalige Mitarbeiter', 'ehemalige Mitarbeiter', 'Alumni', '', '', 1, 0, '', '');";

	
		//////////-
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_lecture_richt_order";
	
		$sql_query[]="CREATE TABLE tx_u2t3_lecture_richt_order (
			`key` int NOT NULL auto_increment,
			sorting int NOT NULL,
			richt varchar(255) NOT NULL,
			description varchar(255) NOT NULL,
			description_en varchar(255) NOT NULL,
			display BOOL NOT NULL, 
			PRIMARY KEY  (`key`)
			) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ";
	
		$sql_query[]='INSERT INTO tx_u2t3_lecture_richt_order (`key`, sorting, richt, description, description_en, display) VALUES 
		    (1, 1, "CBI-BAG-B2", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (2, 2, "CBI-DG-G2", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (3, 3, "CE-BA", "Bachelor Computational Engineering", "Bachelor Computational Engineering", 1),
		    (4, 4, "CE-BA-INF", "Bachelor Computational Engineering", "Bachelor Computational Engineering", 1),
		    (5, 5, "CE-BA-TA-SS", "Bachelor CE, TAF Sensorik", "Bachelor CE, TAF Sensorik", 1),
		    (6, 6, "CE-MA", "Master Computational Engineering", "Master Computational Engineering", 1),
		    (7, 7, "CE-MA", "Master Studiengang Computational Engineering", "Master Studiengang Computational Engineering", 1),
		    (8, 8, "CE-MA-IVF-SWR", "Master Studiengang Computational Engineering", "Master Studiengang Computational Engineering", 1),
		    (9, 9, "EEI-BA", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge",  1),
		    (10, 10, "EEI-DG", "Grundstudium im Diplomstudiengang EE", "Grundstudium im Diplomstudiengang EE", 0),
		    (11, 11, "EEI-DH", "Hauptstudium Dipl. EEI", "Hauptstudium Dipl. EEI", 1),
		    (12, 12, "EEI-DH-AET", "Hauptstudium Dipl. EEI", "Hauptstudium Dipl. EEI", 1),
		    (13, 13, "EEI-DH-AUT", "Hauptstudium Dipl. EEI", "Hauptstudium Dipl. EEI", 1),
		    (14, 14, "EEI-DH-MIK", "Hauptstudium Dipl. EEI", "Hauptstudium Dipl. EEI", 1),
		    (15, 15, "INF-DG", "Grundstudium im Diplomstudiengang Informatik", "Grundstudium im Diplomstudiengang Informatik", 1),
		    (16, 16, "INF-DH-ME", "Hauptstudium im Diplomstudiengang Informatik", "Hauptstudium im Diplomstudiengang Informatik", 1),
		    (17, 17, "INF-DH-MI", "Hauptstudium im Diplomstudiengang Informatik", "Hauptstudium im Diplomstudiengang Informatik", 1),
		    (18, 18, "INF-DH-MINFME", "Hauptstudium im Diplomstudiengang Informatik", "Hauptstudium im Diplomstudiengang Informatik", 1),
		    (19, 19, "IuK-DH-MMS-INF1", "IuK 1", "IuK 1", 0),
		    (20, 20, "IuK-DG", "IuK Diplom Grundstudium", "IuK Diplom Grundstudium", 1),
		    (21, 21, "IuK-DH-MMS-INF1", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (22, 22, "IuK-DH-MMS-INF2", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (23, 23, "MB-BAG", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (24, 24, "MB-DG", "MaschBau Dipl. Grundst.", "MaschBau Dipl. Grundst.", 0),
		    (25, 25, "MB-DG", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (26, 26, "MB-DH", "Hauptstudium Dipl. Maschinenbau", "Hauptstudium Dipl. Maschinenbau", 1),
		    (27, 27, "ME-BA", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (28, 28, "ME-DG", "Grundstudium im Dipl.sg. Mechatronik", "Grundstudium im Diplomstudiengang ME", 0),
		    (29, 29, "ME-DH", "Hauptstudium im Dipl.sg. Mechatronik", "Hauptstudium im Diplomstudiengang ME", 0),
		    (30, 30, "ME-DH", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (31, 31, "SIM-DH", "Hauptstudium im Diplomstudiengang SIM", "Hauptstudium im Diplomstudiengang SIM", 0),
		    (32, 32, "SIM-MA", "Master Studiengang SIM", "Master Studiengang SIM", 0),
		    (33, 33, "SIM-DH", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (34, 34, "SIM-MA", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (35, 35, "WING-DG", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1),
		    (36, 36, "WW-DG", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", "Spezielle Informatikveranstaltungen f&uuml;r andere Studieng&auml;nge", 1)';
	
		////////////
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_lecture_term_order";

	    $sql_query[]='CREATE TABLE tx_u2t3_lecture_term_order (
            `key` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
            `sorting` int NOT NULL,
            `repeat` VARCHAR( 12 ) NOT NULL ,
            `desc` VARCHAR( 12 ) NOT NULL,
            `desc_en` VARCHAR( 12 ) NOT NULL
            )';

	    $sql_query[]='INSERT INTO tx_u2t3_lecture_term_order (`key`, `sorting`, `repeat`, `desc`, `desc_en`) VALUES 
            (1, 1, "w1 1", "Montag", "Monday"),
            (2, 2, "w1 2", "Dienstag", "Tuesday"),
            (3, 3, "w1 3", "Mittwoch", "Wednesday"),
            (4, 4, "w1 4", "Donnerstag", "Thursday"),
            (5, 5, "w1 5", "Freitag", "Friday"),
            (6, 6, "w1 6", "Samstag", "Saturday"),
            (7, 7, "w1 7", "Sonntag", "Sunday")';
	
		////////////
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_lecture_type_order";
	
		$sql_query[]="CREATE TABLE tx_u2t3_lecture_type_order (
			`key` int NOT NULL auto_increment,
			sorting int NOT NULL,
			type varchar(255) NOT NULL,
			description varchar(255) NOT NULL,
			description_plural varchar(255) NOT NULL,
			description_en varchar(255) NOT NULL,
			description_en_plural varchar(255) NOT NULL,
			display BOOL NOT NULL, 
			`media` varchar(255) NOT NULL,
			`introtext` text NOT NULL,
			PRIMARY KEY  (`key`)
			) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ";
	
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 1,  1, "vorl", "Vorlesung", "Vorlesungen", "Lecture", "Lectures", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 2,  2, "v-ue", "Vorlesung und &Uuml;bung", "Vorlesung und &Uuml;bung", "Lecture or Excercise", "Lectures and Excercises", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 3,  3,   "ue", "&Uuml;bung", "&Uuml;bungen", "Exercise", "Exercises", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 4,  4,  "sem", "Seminar", "Seminare", "Seminar", "Seminars", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 5,  5,   "ps", "Proseminar", "Proseminare", "Undergrad. Seminar", "Undergrad. Seminars", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 6,  6,   "hs", "Hauptseminar", "Hauptseminare", "Graduate Seminar", "Graduate Seminars", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 7,  7,   "ag", "Arbeitsgruppe", "Arbeitsgruppen", "Work Group", "Work Groups", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 8,  8,  "gsz", "gsz", "gsz_pl", "gsz_en", "gsz_en_pl", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES ( 9,  9,   "ko", "Kolloquium", "Kolloquien", "Colloquium", "Colloquia", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES (10, 10,   "pr", "Praktikum", "Praktika", "Practical Training", "Practical Trainings", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES (11, 11,   "sl", "sl", "sl_pl", "sl_en", "sl_en_pl", TRUE,"","")';
		$sql_query[]='INSERT INTO tx_u2t3_lecture_type_order VALUES (12, 12,  "tut", "Tutorium", "Tutorien", "Tutorial", "Tutorials", TRUE,"","")';
	
		////////////
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_thesis_status_order";
	
		$sql_query[]="CREATE TABLE tx_u2t3_thesis_status_order (
			`key` int NOT NULL auto_increment,
			sorting int NOT NULL,
			status varchar(255) NOT NULL,
			description varchar(255) NOT NULL,
			description_en varchar(255) NOT NULL,
			display BOOL NOT NULL, 
			PRIMARY KEY  (`key`)
			) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ";
	
		$sql_query[]='INSERT INTO tx_u2t3_thesis_status_order VALUES (1, 1, "open", "Verf&uuml;gbar", "Open", TRUE)';
		$sql_query[]='INSERT INTO tx_u2t3_thesis_status_order VALUES (2, 2, "running", "Laufend", "Running", TRUE)';
		$sql_query[]='INSERT INTO tx_u2t3_thesis_status_order VALUES (3, 3, "finished", "Beendet", "Finished", TRUE)';
	
		////////////
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_thesis_type_order";
	
		$sql_query[]="CREATE TABLE tx_u2t3_thesis_type_order (
			`key` int NOT NULL auto_increment,
			sorting int NOT NULL,
			type varchar(255) NOT NULL,
			description varchar(255) NOT NULL,
			description_plural varchar(255) NOT NULL,
			description_en varchar(255) NOT NULL,
			description_en_plural varchar(255) NOT NULL,
			display BOOL NOT NULL, 
			PRIMARY KEY  (`key`)
			) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ";
	
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (1, 1, "studien", "Studienarbeit", "Studienarbeiten", "Prediploma Thesis", "Prediploma Theses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (2, 2, "diplom", "Diplomarbeit", "Diplomarbeiten", "Diploma Thesis", "Diploma Theses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (3, 3, "bscthes", "Bachelor Arbeit", "Bachelor Arbeiten", "Bachelor Thesis", "Bachelor Theses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (4, 4, "magister", "Magister Arbeit", "Magister Arbeiten", "Magister Thesis", "Magister Theses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (5, 5, "masthes", "Master Arbeit", "Master Arbeiten", "Master Thesis", "Master Theses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (6, 6, "sada", "Studien-/Diplomarbeit", "Studien-/Diplomarbeiten", "Bachelor-/Masterthesis", "Bachelor-/Mastertheses", TRUE)';
		$sql_query[]='INSERT INTO `tx_u2t3_thesis_type_order` VALUES (7, 7, "proj", "Projektarbeit", "Projektarbeiten", "Project Work", "Project Works", TRUE)';
	

		// users including link
		$sql_query[]='DROP VIEW IF EXISTS tx_u2t3_view_persons';

		$sql_query[]="CREATE VIEW tx_u2t3_view_persons AS
			    SELECT tx_u2t3_person.`key`,
				    CONCAT(
					    (
						    CASE WHEN (tx_u2t3_person.firstname IS NULL) OR (tx_u2t3_person.firstname = '') THEN
							    tx_u2t3_person.lastname
						    ELSE
							    CONCAT(tx_u2t3_person.lastname, ', ', tx_u2t3_person.firstname )
						    END
					    )
				    ) AS fullName,
				    CONCAT(
					    (
						    CASE WHEN (u_person_location.url IS NULL) OR (u_person_location.url = '') THEN
							    (
								    CASE WHEN (tx_u2t3_person.firstname IS NULL) OR (tx_u2t3_person.firstname = '') THEN
									    tx_u2t3_person.lastname
								    ELSE
									    CONCAT(tx_u2t3_person.lastname, ', ', tx_u2t3_person.firstname )
								    END
							    )
						    ELSE
							    (
								    CASE WHEN (tx_u2t3_person.firstname IS NULL) OR (tx_u2t3_person.firstname = '') THEN
									    CONCAT( '<a href=".'"'."', u_person_location.url, '\">', tx_u2t3_person.lastname, '</a>' )
								    ELSE
									    CONCAT( '<a href=".'"'."', u_person_location.url, '\">', tx_u2t3_person.lastname, ', ', tx_u2t3_person.firstname, '</a>' )
								    END
							    )
						    END
					    )
				    ) AS link
			    FROM tx_u2t3_person
			    LEFT JOIN u_person_location ON `tx_u2t3_person`.`key` = `u_person_location`.`person_key`;
		";



		// publication authors and editors
		$sql_query[]="DROP VIEW IF EXISTS tx_u2t3_pub_view_authors";
	
		$sql_query[]="CREATE VIEW tx_u2t3_pub_view_authors AS
			    SELECT pub_key,
			    GROUP_CONCAT( tx_u2t3_view_persons.link ORDER BY orderindex ASC SEPARATOR '; ' ) AS authors,
			    GROUP_CONCAT( u_pub_authors.pkey ORDER BY u_pub_authors.orderindex ASC SEPARATOR '; ' ) AS author_keys
			    FROM u_pub_authors
			    LEFT OUTER JOIN tx_u2t3_view_persons ON `tx_u2t3_view_persons`.`key` = `u_pub_authors`.`pkey`
			    GROUP BY pub_key
			    ORDER BY pub_key DESC;
		";
	
		$sql_query[]="DROP VIEW IF EXISTS tx_u2t3_pub_view_editors";
	
		$sql_query[]="CREATE VIEW tx_u2t3_pub_view_editors AS
			    SELECT pub_key, GROUP_CONCAT( tx_u2t3_view_persons.link
			    ORDER BY orderindex ASC
			    SEPARATOR '; ' ) AS editors
			    FROM u_pub_editors
			    LEFT OUTER JOIN tx_u2t3_view_persons ON `tx_u2t3_view_persons`.`key` = `u_pub_editors`.`pkey`
			    GROUP BY pub_key
			    ORDER BY pub_key DESC;
		";

		// Research groups and research projects relation
	
		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_group_projects";
	
		$sql_query[]="CREATE TABLE tx_u2t3_group_projects (
			`uid` int NOT NULL auto_increment,
			`sorting` int NOT NULL,
			group_uid int NOT NULL,
			project_key varchar(255) NOT NULL,
			PRIMARY KEY  (`uid`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin1";
	
		$sql_query[]="INSERT INTO tx_u2t3_group_projects VALUES (1, 1, 8, 'Research.tech.IMMD.IMMD5.3dbild')";
		$sql_query[]="INSERT INTO tx_u2t3_group_projects VALUES (2, 2, 8, 'Research.tech.IMMD.IMMD5.korona')";
		$sql_query[]="INSERT INTO tx_u2t3_group_projects VALUES (3, 3, 18, 'Research.tech.IMMD.IMMD5.verfah')";
		$sql_query[]="INSERT INTO tx_u2t3_group_projects VALUES (4, 4, 14, 'Research.tech.IMMD.IMMD5.unters_1')";
	
//		$sql_query[]="DROP TABLE IF EXISTS tx_u2t3_research_groups";
	
//		$sql_query[]="CREATE TABLE tx_u2t3_research_groups(
//			`uid` int NOT NULL auto_increment,
//			`media` varchar(255) NOT NULL,
//			`introtext` text NOT NULL,
//			PRIMARY KEY  (`uid`)
//			) ENGINE=MyISAM DEFAULT CHARSET=latin1";
	
		foreach ($sql_query as $q)
		{
		    $result = $GLOBALS['TYPO3_DB']->sql_query($q);
		    if(!$result) {
		        echo(mysql_error() . "<br />" . $q . "<br />");
		    }
		}


    } //create_db_static_tables

    function delete_db_data()
    {
		$sql_query = array();
		$sql_query[]='delete from tx_u2t3_job';
		$sql_query[]='delete from tx_u2t3_person'; 
		$sql_query[]='delete from tx_u2t3_title';
		$sql_query[]='delete from tx_u2t3_research';
		$sql_query[]='delete from tx_u2t3_room';
		$sql_query[]='delete from tx_u2t3_room_contacts';
		$sql_query[]='delete from tx_u2t3_lecture'; 
		$sql_query[]='delete from tx_u2t3_thesis';
		$sql_query[]='delete from tx_u2t3_thesis_advisors'; 
		$sql_query[]='delete from tx_u2t3_pub'; 
		$sql_query[]='delete from tx_u2t3_org';
	
		foreach ($sql_query as $q)
		{
		    $GLOBALS['TYPO3_DB']->sql_query($q);
		}
		echo mysql_error();
    } // delete_db_data

    function drop_univis_tables()
    {
		$sql_query = array();
		$sql_query[]='drop table tx_u2t3_job';
		$sql_query[]='drop table tx_u2t3_person'; 
		$sql_query[]='drop table tx_u2t3_title';
		$sql_query[]='drop table tx_u2t3_research';
		$sql_query[]='drop table tx_u2t3_room';
		$sql_query[]='drop table tx_u2t3_room_contacts';
		$sql_query[]='drop table tx_u2t3_lecture'; 
		$sql_query[]='drop table tx_u2t3_thesis';
		$sql_query[]='drop table tx_u2t3_pub';
		$sql_query[]='drop table tx_u2t3_org';
	
		foreach ($sql_query as $q)
		{
		    $GLOBALS['TYPO3_DB']->sql_query($q);
		}
		echo mysql_error();
    } // drop_univis_tables

}
?>
